<?php
/**
 * Created by PhpStorm.
 * User: gakuin
 * Date: 7/7/16
 * Time: 1:19 PM
 */
namespace app\models;

use Yii;
use yii\db\ActiveRecord;
use app\models\User;

class Borkerage extends ActiveRecord
{

    public $brokerage_id;
    public $user_id;
    public $brokerage_money;
    public $orderform_id;
    public $orderform_money;



    public static function getorder($userid){

        return Borkerage::find() -> where(['user_id' => $userid,'del_flag' => '1']) ->asArray() -> all();
    }

    public static function rootgetall(){

        return Borkerage::find() -> asArray()->all();
    }

    //后台获取佣金收益列表
    public function getTableData($limit,$offset,$search){

        $total = (new \yii\db\Query())
            ->from('hcps_borkerage')
            ->where(['del_flag' => 1])
            ->count();
        $queryString = '
        SELECT t1.*, 
          CASE WHEN t1.brokerage_money = t2.max_brokerage_money THEN 1 
               WHEN t1.brokerage_money = t2.min_brokerage_money THEN t2.brokerage_count
               WHEN 
                    t1.brokerage_money > t2.min_brokerage_money 
                    AND t1.brokerage_money < t2.max_brokerage_money THEN 2
          END AS grade FROM hcps_borkerage t1 JOIN
        (SELECT orderform_id, 
          MAX(brokerage_money) AS max_brokerage_money, MIN(brokerage_money) AS min_brokerage_money,
          COUNT(brokerage_money) AS brokerage_count
        FROM hcps_borkerage GROUP BY orderform_id) t2
        ON t1.orderform_id = t2.orderform_id
        WHERE del_flag = 1';
        if (!empty($search)) {
            $queryString = $queryString.' AND t1.orderform_id like :search';
            $queryString = $queryString.' ORDER BY orderform_id DESC, grade LIMIT :limit OFFSET :offset';
            $rows = \Yii::$app->db->createCommand($queryString)
                ->bindValue(':search', '%'.$search.'%')
                ->bindValue(':limit', intval($limit))
                ->bindValue(':offset', intval($offset))
                ->queryAll();
        }
        else {
            $queryString = $queryString.' ORDER BY orderform_id DESC, grade LIMIT :limit OFFSET :offset';
            $rows = \Yii::$app->db->createCommand($queryString)
                ->bindValue(':limit', intval($limit))
                ->bindValue(':offset', intval($offset))
                ->queryAll();
        }
        for($i=0;$i<count($rows);$i++){
            $p_arr= (new \yii\db\Query())->from('hcps_user')->where(['user_id' => $rows[$i]['user_id']])->one();

            $rows[$i]['user_name']=$p_arr['username'];
        }

        $rs=['total'=>$total,'rows'=>$rows];

        return $rs;

    }


    //后台获取佣金打款列表
    public function getYearMonthTableData($limit,$offset,$year,$mon){

        $connection  = Yii::$app->db;
        if($year){
            $yearmon = $year.'-'.$mon;
            $sql     = "SELECT * FROM hcps_borkerage WHERE date_format(create_time,'%Y-%m')='$yearmon' GROUP BY user_id,DATE_FORMAT(create_time, '%Y%m') ";
            $sql1    = "SELECT SUM(brokerage_money) AS brokerage_money_total,
	                        SUM(orderform_money) AS orderform_money_total,
	                        user_id,
	                        is_transfer,
                            DATE_FORMAT(create_time, '%Y-%m') AS createTime
                            FROM hcps_borkerage WHERE date_format(create_time,'%Y-%m')='$yearmon' GROUP BY user_id,DATE_FORMAT(create_time, '%Y%m') ORDER BY createTime DESC
                            LIMIT $offset,$limit";

        }else{
            $sql     = "SELECT * FROM hcps_borkerage WHERE del_flag=1 GROUP BY user_id,DATE_FORMAT(create_time, '%Y%m') ";
            $sql1    = "SELECT SUM(brokerage_money) AS brokerage_money_total,
	                        SUM(orderform_money) AS orderform_money_total,
	                        user_id,
	                        is_transfer,
                            DATE_FORMAT(create_time, '%Y-%m') AS createTime
                            FROM hcps_borkerage WHERE del_flag=1 GROUP BY user_id,DATE_FORMAT(create_time, '%Y%m') ORDER BY createTime DESC
                            LIMIT $offset,$limit";

        }
        $command = $connection->createCommand($sql);
        $res     = $command->queryAll();
        $total = count($res);


        $command1 = $connection->createCommand($sql1);
        $rows     = $command1->queryAll();

        for($i=0;$i<count($rows);$i++){

            $user_name= (new \yii\db\Query())->from('hcps_user')->where(['user_id' => $rows[$i]['user_id']])->one();

            $rows[$i]['user_name']= $user_name['username'];

        }

        $rs=['total'=>$total,'rows'=>$rows];

        return $rs;

    }

    //打款操作
    public function updateTransfer($user_id,$createTime){

        $connection  = Yii::$app->db;
        $sql     = "UPDATE hcps_borkerage SET is_transfer=1 WHERE user_id=$user_id AND date_format(create_time,'%Y-%m')='$createTime'";
        $command = $connection->createCommand($sql);
        $res     = $command->execute();
        return $res;

    }


}